package com.softer.chengxin.score.dao.provider;

import com.softer.chengxin.common.domin.Page;
import com.softer.chengxin.score.entity.ScoreDetailVO;
import org.apache.commons.lang3.StringUtils;

import java.util.HashMap;

/**
 * @author wangxinrui
 * @date 2018/10/12
 */
public class ManagerDaoProvider {

    /**
     * 优良负面信息变更审批
     */
    public String biangeng(ScoreDetailVO s) {
        StringBuffer sql = new StringBuffer(20);
        sql.append("  update score_detail set gmt_modified=now() ");
        if(null != s.getState()){
            sql.append(" ,state=#{state} ");
        }
        if(StringUtils.isNotBlank(s.getScoreTypeId())){
            sql.append(" ,score_type_id=#{scoreTypeId} ");
        }
        if(null != s.getScore()){
            sql.append(" ,score=#{score} ");
        }
        if(StringUtils.isNotBlank(s.getZgBgyj())){
            sql.append(" ,zg_bgyj=#{zgBgyj} ");
        }
        if(StringUtils.isNotBlank(s.getSsnr())){
            sql.append(" ,ssnr=#{ssnr} ");
        }
        if(StringUtils.isNotBlank(s.getZgBgyj())){
            sql.append(" ,zg_bgyj=#{zgBgyj} ");
        }
        if(StringUtils.isNotBlank(s.getSsnr())){
            sql.append(" ,ssnr=#{ssnr} ");
        }
        sql.append(" where id=#{id}");
        System.out.println("修改sql:"+sql.toString());
        return sql.toString();
    }
    /**
     * 获取该企业 年度 的 得分详细（优良信息，负面信息，基本信息）
     */
    public String spNo(HashMap<String,Object> map){
        StringBuffer sql = new StringBuffer(20);
        String shyj = (String)map.get("shyj");
        Integer state = (Integer)map.get("state");
        String approver = (String)map.get("approver");
        String ssnr = (String)map.get("ssnr");
        String score = (String)map.get("score");
        String scoreTypeId = (String)map.get("scoreTypeId");
        Integer id = (Integer)map.get("id");
        sql.append(" update score_detail set state=#{state},shyj=#{shyj},approver=#{approver} ");
        if(StringUtils.isNotBlank(ssnr)){
            sql.append(" ,ssnr=#{ssnr} ");
        }
        if(StringUtils.isNotBlank(score)){
            sql.append(" ,score=#{score} ");
        }
        if(StringUtils.isNotBlank(scoreTypeId)){
            sql.append(" ,score_type_id=#{scoreTypeId} ");
        }
        sql.append("  where id=#{id} ");
        return sql.toString();
    }

    /**
     *  条件查询企业信用年度评价列表
     */
    public String findSRbyCondition(HashMap<String,Object> map){
        StringBuffer sql = new StringBuffer(20);
        String area = (String) map.get("area");
        String qyName = (String) map.get("qyName");
        String dj = (String) map.get("dj");
        String year = (String) map.get("year");
        Page page = (Page) map.get("page");

        sql.append("select @rowno:=@rowno+1 row,c.`name` as qyName,c.business_license_no as businessLicenseNo,r.id as id,r.jc_01 as jc01,r.jc_02 as jc02,r.jc_03 as jc03,r.jc_04 as jc04,r.jc_05 as jc05,r.jc_06 as jc06,r.jc_07 as jc07,r.yl_01 as yl01,r.yl_02 as yl02,r.yl_03 as yl03,r.yl_04 as yl04,r.yl_05 as yl05,r.yl_06 as yl06,r.yl_07 as yl07,r.yl_08 as yl08,r.fm_01 as fm01,r.fm_02 as fm02,r.fm_03 as fm03,r.fm_04 as fm04,r.fm_05 as fm05,r.fm_06 as fm06,r.fm_07 as fm07,r.all_score as allScore,r.poguid as poguid,DATE_FORMAT(r.gmt_create, '%Y-%m-%d') as gmtCreate,r.dj as dj,r.year as year,DATE_FORMAT(r.gmt_modified, '%Y-%m-%d') as gmtModified ");
        sql.append(" from score_record r,company_base_info c,(select @rowno:=0) rn ");
        sql.append(" where c.state = 2 and c.id=r.poguid ");

        commonSql(sql,area,qyName,dj,year);

        sql.append(" LIMIT ");
        sql.append(page.getBeginIndex()).append(",").append(page.getLimit());
        return sql.toString();
    }

    /**
     *  条件查询企业信用年度评价列表 条数
     */
    public String findSRbyConditionCount(HashMap<String,Object> map){
        StringBuffer sql = new StringBuffer(20);
        String area = (String) map.get("area");
        String qyName = (String) map.get("qyName");
        String dj = (String) map.get("dj");
        String year = (String) map.get("year");

        sql.append("select count(*) from score_record r,company_base_info c,(select @rowno:=0) rn ");
        sql.append(" where c.state = 2 and c.id=r.poguid ");

        commonSql(sql,area,qyName,dj,year);

        return sql.toString();
    }

    public void commonSql(StringBuffer sql,String area,String qyName,String dj,String year){
        if(StringUtils.isNotBlank(area)){
            sql.append(" and c.community in (").append(area).append(")");
        }
        if(StringUtils.isNotBlank(qyName)){
            sql.append(" and c.`name` like CONCAT('%','").append(qyName).append("','%')");
        }
        if(StringUtils.isNotBlank(dj)){
            sql.append(" and r.dj=#{dj}");
        }
        if (StringUtils.isNotBlank(year)){
            sql.append(" and r.year=#{year}");
        }else {
            sql.append(" and r.year=DATE_FORMAT(NOW(), '%Y')");
        }
    }

    /**
     * 主管部门查看自己管辖区域内企业的 优良/负面信息列表
     */
    public String getScoreDetailList(HashMap<String,Object> map) {
        StringBuffer sql = new StringBuffer(20);
        Page page = (Page) map.get("page");
        String area = (String) map.get("area");
        String qyName = (String) map.get("qyName");
        String proName = (String) map.get("proName");
        String state = (String) map.get("state");

        sql.append("select @rowno:=@rowno+1 as row,c.`name` as qyName,p.project_name as proName,d.id as id,d.score_type_id AS scoreTypeId,(SELECT score_name FROM score_type WHERE id = d.score_type_id) AS scoreTypeName,d.score as score,d.corp_id as corpId,d.project_id as projectId,d.adder as adder,d.remark as remark,d.reason as reason,d.state as state,m.name as approver,DATE_FORMAT(d.approver_time, '%Y-%m-%d') as approverTime,DATE_FORMAT(d.effective_begin_time, '%Y-%m-%d') as effectiveBeginTime,DATE_FORMAT(d.effective_end_time, '%Y-%m-%d') as effectiveEndTime,d.info_type as infoType,d.bjdw as bjdw,DATE_FORMAT(d.bjsj, '%Y-%m-%d') as bjsj,d.hjmc as hjmc,d.file_path_id as filePathId,d.shyj as shyj,d.ssnr as ssnr,d.ssfj_id as ssfjId,d.sssj as sssj,d.hjxm as hjxm,DATE_FORMAT(d.gmt_create, '%Y-%m-%d') as gmtCreate,DATE_FORMAT(d.gmt_modified, '%Y-%m-%d') as gmtModified,DATE_FORMAT(d.publicity_begin_time, '%Y-%m-%d') as publicityBeginTime,DATE_FORMAT(d.publicity_end_time, '%Y-%m-%d') as publicityEndTime ");
        sql.append(" from score_detail d left join company_base_info c on c.id=d.corp_id left join company_project p on d.project_id=p.id left join manager_user m on m.login_name=d.approver,(select @rowno:=#{page}) rn");
        sql.append(" where d.info_type=#{infoType} ");
        if(StringUtils.isNotBlank(area)){
            sql.append(" and IF(d.project_id=0,c.community in (").append(area).append("),");
            sql.append("p.community in (").append(area).append("))");
        }
        if(StringUtils.isNotBlank(qyName)) {
            sql.append(" and c.`name` like CONCAT('%',#{qyName},'%')");
        }
        if(StringUtils.isNotBlank(proName)) {
            sql.append(" and p.project_name like CONCAT('%',#{proName},'%')");
        }
        if(StringUtils.isNotBlank(state)) {
            sql.append(" and d.state in (").append(state).append(")");
        } else {
            sql.append(" and d.state in (1,2,3,4,5,6,7,9,10,11,12)");
        }
        sql.append(" LIMIT ");
        sql.append(page.getBeginIndex()).append(",").append(page.getLimit());
        return sql.toString();
    }

    /**
     * 主管部门查看自己管辖区域内企业的 优良/负面信息列表 条数
     */
    public String getScoreDetailListCount(HashMap<String,Object> map) {
        StringBuffer sql = new StringBuffer(20);
        String area = (String) map.get("area");
        String qyName = (String) map.get("qyName");
        String proName = (String) map.get("proName");
        String state = (String) map.get("state");

        sql.append("select count(*) ");
        sql.append(" from score_detail d left join company_base_info c on c.id=d.corp_id left join company_project p on d.project_id=p.id");
        sql.append(" where d.info_type=#{infoType}");
        if(StringUtils.isNotBlank(area)) {
            sql.append(" and IF(d.project_id=0,c.community in (").append(area).append("),");
            sql.append("p.community in (").append(area).append("))");
        }
        if(StringUtils.isNotBlank(qyName)) {
            sql.append(" and c.`name` like CONCAT('%',#{qyName},'%')");
        }
        if(StringUtils.isNotBlank(proName)) {
            sql.append(" and p.project_name like CONCAT('%',#{proName},'%')");
        }
        if(StringUtils.isNotBlank(state)) {
            sql.append(" and d.state in (").append(state).append(")");
        } else {
            sql.append(" and d.state in (1,2,3,4,5,6,7,9,10,11,12)");
        }
        return sql.toString();
    }

    /**
     * 获取负面信息录入界面的信息列表
     */
    public String getScoreAddList(HashMap<String,Object> map){
        StringBuffer sql = new StringBuffer(20);
        String adder = (String) map.get("adder");
        String qyName = (String) map.get("qyName");
        String proName = (String) map.get("proName");
        Integer infoType = (Integer) map.get("infoType");
        Page page = (Page) map.get("page");

        sql.append("select @rowno:=@rowno+1 as row,c.`name` as qyName,");
        sql.append(" d.id as id,d.score_type_id AS scoreTypeId,(SELECT score_name FROM score_type WHERE id = d.score_type_id) AS scoreTypeName,d.score as score,d.corp_id as corpId,d.project_id as projectId,d.adder as adder,d.remark as remark,d.reason as reason,d.state as state,d.approver as approver,DATE_FORMAT(d.approver_time, '%Y-%m-%d') as approverTime,DATE_FORMAT(d.effective_begin_time, '%Y-%m-%d') as effectiveBeginTime,DATE_FORMAT(d.effective_end_time, '%Y-%m-%d') as effectiveEndTime,d.info_type as infoType,d.bjdw as bjdw,DATE_FORMAT(bjsj, '%Y-%m-%d') as bjsj,d.hjmc as hjmc,d.file_path_id as filePathId,d.shyj as shyj,d.ssnr as ssnr,d.ssfj_id as ssfjId,d.sssj as sssj,d.hjxm as hjxm,DATE_FORMAT(d.gmt_create, '%Y-%m-%d') as gmtCreate,DATE_FORMAT(d.gmt_modified, '%Y-%m-%d') as gmtModified,DATE_FORMAT(d.publicity_begin_time, '%Y-%m-%d') as publicityBeginTime,DATE_FORMAT(d.publicity_end_time, '%Y-%m-%d') as publicityEndTime ");
        sql.append(" from score_detail d left join company_base_info c on c.id=d.corp_id left join company_project p on d.project_id=p.id,(select @rowno:=1) rn \n");
        sql.append(" where d.adder='").append(adder).append("'");
        sql.append(" and d.state=0");
        sql.append(" and d.info_type=").append(infoType);
        if(StringUtils.isNotBlank(qyName)) {
            sql.append(" and c.`name` like CONCAT('%','").append(qyName).append("','%')");
        }
        if(StringUtils.isNotBlank(proName)) {
            sql.append(" and p.project_name like CONCAT('%','").append(proName).append("','%')");
        }

        sql.append(" LIMIT ");
        sql.append(page.getBeginIndex()).append(",").append(page.getLimit());
        return sql.toString();
    }
    /**
     * 获取负面信息录入界面的信息列表 条数
     */
    public String getScoreAddListCount(HashMap<String,Object> map){
        StringBuffer sql = new StringBuffer(20);
        String adder = (String) map.get("adder");
        String qyName = (String) map.get("qyName");
        String proName = (String) map.get("proName");
        Integer infoType = (Integer) map.get("infoType");

        sql.append("select count(*)");
        sql.append(" from score_detail d left join company_base_info c on c.id=d.corp_id left join company_project p on d.project_id=p.id,(select @rowno:=1) rn \n");
        sql.append(" where d.adder='").append(adder).append("'");
        sql.append(" and d.state=0");
        sql.append(" and d.info_type=").append(infoType);
        if(StringUtils.isNotBlank(qyName)) {
            sql.append(" and c.`name` like CONCAT('%','").append(qyName).append("','%')");
        }
        if(StringUtils.isNotBlank(proName)) {
            sql.append(" and p.project_name like CONCAT('%','").append(proName).append("','%')");
        }

        return sql.toString();
    }

    /**
     * 获取该企业 年度 的 得分详细（优良信息，负面信息，基本信息）
     */
    public String getScoreDetailByYear(HashMap<String,Object> map){
        StringBuffer sql = new StringBuffer(20);
        Page page = (Page) map.get("page");
        String area = (String)map.get("area");
        String qyName = (String)map.get("qyName");
        Integer infoType = (Integer)map.get("infoType");
        String effectiveBeginTime = (String)map.get("effectiveBeginTime");
        String effectiveEndTime = (String)map.get("effectiveEndTime");
        Integer corpId = (Integer) map.get("corpId");
        String year = (String)map.get("year");

        sql.append("select @rowno:=@rowno+1 as row,c.`name` as qyName,d.id as id,d.score_type_id AS scoreTypeId,(SELECT score_name FROM score_type WHERE id = d.score_type_id) AS scoreTypeName,d.score as score,d.corp_id as corpId,d.project_id as projectId,d.adder as adder,d.remark as remark,d.reason as reason,d.state as state,d.approver as approver,DATE_FORMAT(d.approver_time, '%Y-%m-%d') as approverTime,DATE_FORMAT(d.effective_begin_time, '%Y-%m-%d') as effectiveBeginTime,DATE_FORMAT(d.effective_end_time, '%Y-%m-%d') as effectiveEndTime,d.info_type as infoType,d.bjdw as bjdw,DATE_FORMAT(d.bjsj, '%Y-%m-%d') as bjsj,d.hjmc as hjmc,d.file_path_id as filePathId,d.shyj as shyj,d.ssnr as ssnr,d.ssfj_id as ssfjId,d.sssj as sssj,d.hjxm as hjxm,DATE_FORMAT(d.gmt_create, '%Y-%m-%d') as gmtCreate,DATE_FORMAT(d.gmt_modified, '%Y-%m-%d') as gmtModified,DATE_FORMAT(d.publicity_begin_time, '%Y-%m-%d') as publicityBeginTime,DATE_FORMAT(d.publicity_end_time, '%Y-%m-%d') as publicityEndTime ");
        sql.append(" from score_detail d,company_base_info c,(select @rowno:=#{page}) rn");
        sql.append(" where c.id=d.corp_id and c.state in (1,2,3) and d.state=7");

        commonCode(sql,area,qyName,infoType,effectiveBeginTime,effectiveEndTime,corpId,year);

        sql.append(" LIMIT ");
        sql.append(page.getBeginIndex()).append(",").append(page.getLimit());

        return sql.toString();
    }

    /**
     * 获取该企业 年度 的 得分详细（优良信息，负面信息，基本信息） 条数
     */
    public String getScoreDetailByYearCount(HashMap<String,Object> map){
        StringBuffer sql = new StringBuffer(20);
        String area = (String)map.get("area");
        String qyName = (String)map.get("qyName");
        Integer infoType = (Integer)map.get("infoType");
        String effectiveBeginTime = (String)map.get("effectiveBeginTime");
        String effectiveEndTime = (String)map.get("effectiveEndTime");
        Integer corpId = (Integer) map.get("corpId");
        String year = (String)map.get("year");

        sql.append("select count(*)");
        sql.append(" from score_detail d,company_base_info c");
        sql.append(" where c.id=d.corp_id and c.state in (1,2,3) and d.state=7");

        commonCode(sql,area,qyName,infoType,effectiveBeginTime,effectiveEndTime,corpId,year);

        return sql.toString();
    }

    public void commonCode(StringBuffer sql,String area,String qyName,Integer infoType,String effectiveBeginTime,String effectiveEndTime,Integer corpId,String year){
        if (StringUtils.isNotBlank(area)){
            sql.append(" and c.community in(").append(area).append(")");
        }
        if (StringUtils.isNotBlank(qyName)){
            sql.append(" and c.`name` like CONCAT('%','").append(area).append("','%')");
        }
        if (infoType!=null){
            sql.append(" and d.info_type=").append(infoType);
        }
        if (StringUtils.isNotBlank(effectiveBeginTime)){
            sql.append(" and d.effective_begin_time>=").append(effectiveBeginTime);
        }
        if (StringUtils.isNotBlank(effectiveEndTime)){
            sql.append(" and d.effective_end_time<=").append(effectiveEndTime);
        }
        if (corpId!=null){
           sql.append(" and d.corp_id=").append(corpId);
        }
        if (StringUtils.isNotBlank(year)){
            sql.append(" and year(d.effective_begin_time)=").append(year);
        }
    }

    /**
     * 获取变更页面列表
     */
    public String getBianGengList(HashMap<String,Object> map){
        StringBuffer sql = new StringBuffer(20);
        Page page = (Page) map.get("page");
        String qyName = (String) map.get("qyName");
        String proName = (String) map.get("proName");
        String state = (String) map.get("state");
        sql.append("select c.`name` as qyName,p.project_name as proName,d.id as id,d.score_type_id AS scoreTypeId,(SELECT score_name FROM score_type WHERE id = d.score_type_id) AS scoreTypeName,d.score as score,d.corp_id as corpId,d.project_id as projectId,d.adder as adder,d.remark as remark,d.reason as reason,d.state as state,d.approver as approver,DATE_FORMAT(d.approver_time, '%Y-%m-%d') as approverTime,DATE_FORMAT(d.effective_begin_time, '%Y-%m-%d') as effectiveBeginTime,DATE_FORMAT(d.effective_end_time, '%Y-%m-%d') as effectiveEndTime,d.info_type as infoType,d.bjdw as bjdw,DATE_FORMAT(d.bjsj, '%Y-%m-%d') as bjsj,d.hjmc as hjmc,d.file_path_id as filePathId,d.shyj as shyj,d.ssnr as ssnr,d.ssfj_id as ssfjId,d.sssj as sssj,d.hjxm as hjxm,DATE_FORMAT(d.gmt_create, '%Y-%m-%d') as gmtCreate,DATE_FORMAT(d.gmt_modified, '%Y-%m-%d') as gmtModified,DATE_FORMAT(d.publicity_begin_time, '%Y-%m-%d') as publicityBeginTime,DATE_FORMAT(d.publicity_end_time, '%Y-%m-%d') as publicityEndTime ");
        sql.append(" from score_detail d left join company_base_info c on c.id=d.corp_id left join company_project p on d.project_id=p.id");
        sql.append(" where d.info_type=#{infoType} ");
        if (StringUtils.isNotBlank(state)) {
            sql.append(" and d.state in (").append(state).append(")");
        }
        if(StringUtils.isNotBlank(qyName)) {
            sql.append(" and c.`name` like CONCAT('%',#{qyName},'%')");
        }
        if(StringUtils.isNotBlank(proName)) {
            sql.append(" and p.project_name like CONCAT('%',#{proName},'%')");
        }
        sql.append(" LIMIT ");
        sql.append(page.getBeginIndex()).append(",").append(page.getLimit());
        return sql.toString();
    }
    /**
     * 获取变更页面列表 条数
     */
    public String getBianGengListCount(HashMap<String,Object> map){
        StringBuffer sql = new StringBuffer(20);
        String qyName = (String) map.get("qyName");
        String proName = (String) map.get("proName");
        String state = (String) map.get("state");
        sql.append("select count(*) ");
        sql.append(" from score_detail d left join company_base_info c on c.id=d.corp_id left join company_project p on d.project_id=p.id");
        sql.append(" where d.info_type=#{infoType} ");
        if (StringUtils.isNotBlank(state)) {
            sql.append(" and d.state in (").append(state).append(")");
        }
        if(StringUtils.isNotBlank(qyName)) {
            sql.append(" and c.`name` like CONCAT('%',#{qyName},'%')");
        }
        if(StringUtils.isNotBlank(proName)) {
            sql.append(" and p.project_name like CONCAT('%',#{proName},'%')");
        }
        return sql.toString();
    }

}
